package com.lenovo.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBHelper {
    public static final String name = "com.mysql.jdbc.Driver";

    public static final String url = "jdbc:mysql://10.122.23.164:3306/lenovo_upp";
    public static final String user = "upp";
    public static final String password = "Lenovo!23Upp";

//    public static final String url = "jdbc:mysql://10.96.91.186:3306/lenovo_upp_test";
//    public static final String user = "root";
//    public static final String password = "123456";

    public Connection conn = null;  
    public PreparedStatement pst = null;


  
    public DBHelper(String sql) {  
        try {  
            Class.forName(name);//指定连接类型  
            conn = DriverManager.getConnection(url, user, password);//获取连接  
            pst = conn.prepareStatement(sql);//准备执行语句  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
  
    public void close() {  
        try {  
            this.conn.close();  
            this.pst.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }


    public static int getNewUserConf(){
        String sql = "select content from tb_feature_rule where fid = 150";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return 6;
    }


    public static int getTitleChangeConf(){
        String sql = "select content from tb_feature_rule where fid = 178";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return 3;
    }


    public static String getAppConf(){
        String sql = "select content from tb_feature_rule where fid = 180";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return "1-10";
    }

    public static String getNotAppConf(){
        String sql = "select content from tb_feature_rule where fid = 181";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return "1-5";
    }

    //Peak within Day
    public static String getPeakWithinDay(){
        String sql = "select content from tb_feature_rule where fid = 182";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return "07:00-10:00-14:00-18:00-23:00";
    }

    public static String getL0Conf(){
        String sql = "select content from tb_feature_rule where fid = 193";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return "1-1-1";
    }

   // Ticket Category Ratio
   public static String getTicket_Category_Ratio_Conf(){
       String sql = "select content from tb_feature_rule where fid = 157";//SQL语句
       DBHelper db = new DBHelper(sql);//创建DBHelper对象
       ResultSet ret =null;
       try {
           ret = db.pst.executeQuery();//执行语句，得到结果集
           if(ret!=null)
               return ret.getString(1);
       } catch (SQLException e) {
           e.printStackTrace();
       }finally {
           try {
               ret.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           db.close();//关闭连接
       }
       return "30-50-90";
   }

    public static List getBU(){
        String sql = "select lower(t.managerid),t.bname FROM tb_rule_mapping t where t.bname is not null AND LOWER(t.bname) != 'null' AND t.bname !=''";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        List<String> all = new ArrayList<String>();
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            while (ret.next())
                all.add(ret.getString(1) + "#" + ret.getString(2));
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return all;
    }
    public static List getSegment(){
        String sql = "select lower(t.managerid),t.sname FROM tb_rule_mapping t where t.sname is not null AND LOWER(t.sname) != 'null' AND t.sname !=''";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        List<String> all = new ArrayList<String>();
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            while (ret.next())
                all.add(ret.getString(1) + "#" + ret.getString(2));
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return all;
    }
    public static List get_Pcg_dcg(){
        String sql = "select lower(t.managerid),t.pname FROM tb_rule_mapping t where t.pname is not null AND LOWER(t.pname) != 'null' AND t.pname !=''";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        List<String> all = new ArrayList<String>();
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            while (ret.next())
                all.add(ret.getString(1) + "#" + ret.getString(2));
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return all;
    }

    public static int getUrgencyConf(){
        String sql = "select content from tb_feature_rule where fid = 189";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return 1;
    }

    public static int getComplimentConf(){
        String sql = "select content from tb_feature_rule where fid = 191";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return 1;
    }
    public static int getComplaintConf() {
        String sql = "select content from tb_feature_rule where fid = 190";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret = null;
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            if (ret.next())
                return ret.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return 1;
    }
    public static List get_user_level(){
        String sql = "select itcode,`level` from tb_user_level";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        List<String> all = new ArrayList<String>();
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            while (ret.next())
                all.add(ret.getString(1) + "#" + ret.getString(2));
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return all;
    }

    public static List get_country_map(){
        String sql = "select Workday_Country,Traget_Country  from tb_country_mapping";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        List<String> all = new ArrayList<String>();
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            while (ret.next())
                all.add(ret.getString(1) + "#" + ret.getString(2));
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return all;
    }

    public static List get_city_map(){
        String sql = "select city,city_should_be from tb_city_mapping";//SQL语句
        DBHelper db = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret =null;
        List<String> all = new ArrayList<String>();
        try {
            ret = db.pst.executeQuery();//执行语句，得到结果集
            while (ret.next())
                all.add(ret.getString(1) + "#" + ret.getString(2));
            return all;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ret.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            db.close();//关闭连接
        }
        return all;
    }


    public static void main(String[] args) {

        System.out.print(getL0Conf());
    }



} 